﻿using System;
using System.Collections.Generic;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.Util;
using System.IO;
using System.Linq;
using NPOI.XSSF.UserModel;

namespace bruke_plugs
{
    public static class NPOI_Helper
    {
        //NPOI里面的管理层次为：workbook->worksheet->row->cell.
        //database->table表>record记录->field字段
        public static void Show()
        {
            //var filepath = @"C:\Users\Administrator\Desktop\权限整理2.xls";
            var filepath = @"C:\Users\Administrator\Desktop\权限整理3.xlsx";

            //workeBook 库
            XSSFWorkbook wb;//XSSFWorkbook-->2017  HSSFWorkbook-->2003 版本不一样，使用不同的UserModel
            FileStream file;
            file = new FileStream(filepath, FileMode.Open, FileAccess.Read);
            wb = new XSSFWorkbook(file);

            //Sheet 表
            XSSFSheet sheet;
            sheet = wb.GetSheet("Sheet1") as XSSFSheet;
            //wb.CreateSheet(sheet的名称） //创建一表

            //row 行
            //sheet.CreateRow(0); //创建一行
            for (int i = 1; i < 5000; i++)
            {
                int departmentCellIndex6 = 6;

                //sheet.GetRow(i).GetCell(i) 获取单元格

                var departmentPathName = sheet.GetRow(i).GetCell(departmentCellIndex6).ToString();
                if (string.IsNullOrEmpty(departmentPathName))
                    break;

                var departmentNames = departmentPathName.Split('-').ToList();

                var department1 = departmentNames.First();
                var department2 = departmentNames.Count >= 2 ? departmentNames[1] : "";
                var department3 = departmentNames.Count >= 3 ? departmentNames[2] : department1;
                
                //SetCellValue//设置单元格值
                //sheet.GetRow(i).GetCell(departmentCellIndex6 - 1).SetCellValue(department1);
                //sheet.GetRow(i).GetCell(departmentCellIndex6).SetCellValue(department2);
                //sheet.GetRow(i).GetCell(departmentCellIndex6+1).SetCellValue(department3);
            }
            file = new FileStream(filepath, FileMode.Open, FileAccess.Write);
            wb.Write(file);
            file.Close();
            wb.Close();

        }

    }
}
